package com.guigu.serviceorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.guigu.serviceorder.entity.TbStatistical;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author jobob
 * @since 2020-07-17
 */
public interface TbStatisticalMapper extends BaseMapper<TbStatistical> {
    /**
     * 下单人数
     * @return
     */
    @Select("SELECT COUNT(*) number  FROM (SELECT COUNT(*) FROM tb_order GROUP BY username) oo")
    TbStatistical selectnumber();

    /**
     * 订单数和下单金额
     * @return
     */
    @Select("SELECT COUNT(*) orders ,SUM(pre_money) omoney FROM tb_order")
    TbStatistical selectorders();

    /**
     * 下单件数
     * @return
     */
    @Select("SELECT SUM(i.num) onum FROM tb_order o,tb_order_item i WHERE o.`id`=i.order_id")
    TbStatistical selectonum();

    /**
     * 有效订单数
     * @return
     */
    @Select("SELECT COUNT(*) ovalid  FROM tb_order  WHERE order_status!=4")
    TbStatistical selectovalid();

    /**
     * 退款金额
     * @return
     */
    @Select("SELECT SUM(total_money) amount FROM tb_order WHERE pay_status=2")
    TbStatistical selectamount();

    /**
     * 付款人数
     * @return
     */
    @Select("SELECT COUNT(*) payment FROM (SELECT * FROM tb_order WHERE pay_status=1 GROUP BY username) oo")
    TbStatistical selectpayment();

    /**
     * 付款件数和付款金额以及客单价
     * @return
     */
    @Select("SELECT SUM(total_num)quantity ,SUM(total_money)pamount ,SUM(pay_money) price  FROM tb_order WHERE pay_status=1 \n")
    TbStatistical selectquantity();


}
